﻿
using EA.Common;
using EA.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace EA.DAO
{
    public class ProductDA : ObjectDA
    {
        public ProductDA(String connection_string)
            : base(connection_string)
        {

        }

        public DataTable GetListProduct(int index1,int index2){
            String query = "SELECT  * "
                            + " FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY ID ) AS IndexRow, * "
                            + " FROM Product  ) AS RowConstrainedResult "
                            + " WHERE   IndexRow >= @Index1 AND IndexRow <= @Index2 "
                            + " ORDER BY IndexRow ";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@Index1", index1);
            param[1] = new SqlParameter("@Index2", index2);
            DataTable table = new DataTable();
            this.Fill(query, param, table);
            return table;
        }

        public DataTable GetListProduct()
        {
            String query = "SELECT  * FROM Product";
            DataTable table = new DataTable();
            this.Fill(query, null, table);
            return table;
        }

        public DataTable GetTotalProduct()
        {
            String query = "SELECT  COUNT(*) AS TOTAL FROM Product";
            DataTable table = new DataTable();
            this.Fill(query, null, table);
            return table;
        }

        public DataTable GetTotalProductByKeword(String keyword)
        {
            String query = "SELECT COUNT(*) AS TOTAL FROM Product A  WHERE (name like @keyword OR detail like @keyword2) AND status <> @status ";
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@keyword", "%" + keyword + "%");
            param[1] = new SqlParameter("@keyword2", "%" + keyword + "%");
            param[2] = new SqlParameter("@status", Constant.PRODUCT_PENDING_APPROVAL);
            DataTable table = new DataTable();
            this.Fill(query, param, table);
            return table;
        }

        public DataTable GetProductByID(int ID)
        {
            String query = "SELECT *,(SELECT COUNT(*) FROM LikeDislike L WHERE L.ProductID = P.ID AND L.LikeDislike = "+ Constant.LIKE+" ) As TotalLike FROM Product P WHERE ID=@ID";
            SqlParameter[] param=new SqlParameter[1];
            param[0] = new SqlParameter("@ID",ID);
            DataTable table = new DataTable();
            this.Fill(query,param,table);
            return table;
        }

        public DataTable GetValidAuctionProductByID(int ID)
        {
            String query = "SELECT *,(SELECT COUNT(*) FROM LikeDislike L WHERE L.ProductID = P.ID AND L.LikeDislike = " + Constant.LIKE + " ) As TotalLike FROM Product P WHERE ID=@ID AND Status=@status and DateDiff(MINUTE,BeginDate,@currentDate) <= (TotalBidDay*24*60 + PlusMinute) ";
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@ID", ID);
            param[1] = new SqlParameter("status", Constant.PRODUCT_ACTIVE_AUCTION);
            param[2] = new SqlParameter("currentDate", StringUtil.GetSQLDate());
            DataTable table = new DataTable();
            this.Fill(query, param, table);
            return table;
        }

        public Boolean DeleteProduct(int ID)
        {
            String query = "DELETE Product WHERE ID=@ID";
            SqlParameter[] param=new SqlParameter[1];
            param[0] = new SqlParameter("@ID",ID);
            return this.ExecuteNonQuery(query,param);
        }

        public Boolean UpdateProductByParam(int ID, int status, DateTime beginDate, int productCategoryID)
        {
            String query = "UPDATE Product SET Status=@status,BeginDate=@beginDate,ProductCategoryID=@productCategoryID, TotalBidDay="+Constant.TOTALBIDDAY+" WHERE ID=@ID";
            SqlParameter[] param=new SqlParameter[4];
            param[0] = new SqlParameter("ID",ID);
            param[1] = new SqlParameter("status",status);
            param[2] = new SqlParameter("beginDate",beginDate);
            param[3] = new SqlParameter("productCategoryID", productCategoryID);
            return this.ExecuteNonQuery(query,param);
        }

        public Boolean UpdateProductByPlusMinute(int ID, DateTime currentDate)
        {
            String query = "UPDATE Product SET PlusMinute=PlusMinute+" + Constant.MINUTE_END + " WHERE ID=@ID AND (TotalBidDay*24*60-PlusMinute)-DateDiff(MINUTE,@currentDate,BeginDate)>1 AND (TotalBidDay*24*60-PlusMinute)-DateDiff(MINUTE,BeginDate,@currentDate)<=" + Constant.MINUTE_END + "";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("ID", ID);
            param[1] = new SqlParameter("currentDate", currentDate);
            return this.ExecuteNonQuery(query, param);
        }

        public Boolean UpdateProductByCurrentPrice(int ID,Decimal currrentPrice)
        {
            String query = "UPDATE Product SET CurrentPrice=@currrentPrice WHERE ID=@ID";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("ID", ID);
            param[1] = new SqlParameter("currrentPrice", currrentPrice);
            return this.ExecuteNonQuery(query, param);
        }

        public DataTable GetNewestListProduct(int top)
        {
            String query = "SELECT top " + top + " *,(SELECT TOP 1 Url FROM ProductImage PI WHERE PI.ProductId = P.ID  ) As URL FROM Product P WHERE Status=@status and DateDiff(MINUTE,BeginDate,@currentDate) <= (TotalBidDay*24*60 + PlusMinute) ORDER BY ID DESC";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("status", Constant.PRODUCT_ACTIVE_AUCTION);
            param[1] = new SqlParameter("currentDate", StringUtil.GetSQLDate());
            
            DataTable table = new DataTable();
            this.Fill(query,param,table);
            return table;
        }

        public DataTable GetListProductByCategory(int top, int categoryID)
        {
            String query = "SELECT top " + top + " *,(SELECT TOP 1 Url FROM ProductImage PI WHERE PI.ProductId = P.ID  ) As URL FROM Product P WHERE Status=@status and ProductCategoryID=@categoryID and DateDiff(MINUTE,BeginDate,@currentDate) <= (TotalBidDay*24*60 + PlusMinute ) ORDER BY ID DESC";
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("categoryID", categoryID);
            param[1] = new SqlParameter("status", Constant.PRODUCT_ACTIVE_AUCTION);
            param[2] = new SqlParameter("currentDate", StringUtil.GetSQLDate());
            
            DataTable table = new DataTable();
            this.Fill(query, param, table);
            return table;
        }

        public DataTable GetListProductByCategoryAndRating(int top, int categoryID)
        {
            String query = "SELECT top " + top + " *,(SELECT TOP 1 Url FROM ProductImage PI WHERE PI.ProductId = P.ID  ) As URL FROM Product P WHERE BuyerRating IS NOT NULL AND BuyerRating >0 AND Status=@status and ProductCategoryID=@categoryID and DateDiff(MINUTE,BeginDate,@currentDate) > (TotalBidDay*24*60 + PlusMinute)  ORDER BY BuyerRating DESC";
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("categoryID", categoryID);
            param[1] = new SqlParameter("status", Constant.PRODUCT_ACTIVE_AUCTION);
            param[2] = new SqlParameter("currentDate", StringUtil.GetSQLDate());

            DataTable table = new DataTable();
            this.Fill(query, param, table);
            return table;
        }

        public DataTable GetListProductAreLike(int top)
        {
            String query = " SELECT TOP " + top + " C.* FROM (SELECT  A.*,(SELECT TOP 1 Url FROM ProductImage PI WHERE PI.ProductId = A.ID  ) As URL,(SELECT COUNT(*) FROM LikeDislike B WHERE B.ProductID = A.ID AND B.LikeDisLike = " + Constant.LIKE + "  ) AS TotalLike FROM Product A WHERE Status=@status and DateDiff(MINUTE,BeginDate,@currentDate) <= (TotalBidDay*24*60 + PlusMinute) ) AS C ORDER BY C.TotalLike DESC";
            SqlParameter[] param=new SqlParameter[3];
            
            param[0] = new SqlParameter("top",top);
            param[1] = new SqlParameter("status", Constant.PRODUCT_ACTIVE_AUCTION);
            param[2] = new SqlParameter("currentDate", StringUtil.GetSQLDate());
            
            DataTable table = new DataTable();
            this.Fill(query,param,table);
            return table;
        }

        public DataTable GetListProductAreRating(int top)
        {
            String query = " SELECT top " + top + " *,(SELECT TOP 1 Url FROM ProductImage PI WHERE PI.ProductId = P.ID  ) As URL FROM Product P WHERE BuyerRating IS NOT NULL AND BuyerRating >0 ORDER BY BuyerRating DESC";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("top", top);
            DataTable table = new DataTable();
            this.Fill(query, param, table);
            return table;
        }


        public DataTable GetListProductByKeyword(int index1, int index2,String keyword)
        {
            String query = "SELECT  * "
                            + " FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY ID ) AS IndexRow, *, (SELECT TOP 1 Url FROM ProductImage PI WHERE PI.ProductId = A.ID  ) As URL  "
                            + " FROM Product A WHERE (name like @keyword OR detail like @keyword2) AND status <> @status ) AS RowConstrainedResult "
                            + " WHERE   IndexRow >= @Index1 AND IndexRow <= @Index2 "
                            + " ORDER BY IndexRow ";
            SqlParameter[] param = new SqlParameter[5];
            param[0] = new SqlParameter("@Index1", index1);
            param[1] = new SqlParameter("@Index2", index2);
            param[2] = new SqlParameter("@keyword", "%"+keyword+"%");
            param[3] = new SqlParameter("@keyword2", "%" + keyword + "%");
            param[4] = new SqlParameter("@status", Constant.PRODUCT_PENDING_APPROVAL);
            DataTable table = new DataTable();
            this.Fill(query, param, table);
            return table;
        }


        public Boolean UpdateRatingByID(int ID,int rating)
        {
            String query = "UPDATE Product SET BuyerRating=@rating  WHERE ID=@ID ";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("ID", ID);
            param[1] = new SqlParameter("rating", rating);
            return this.ExecuteNonQuery(query, param);
        }


        public Boolean InsertProduct(ProductTO productTO)
        {
            String query = "Insert INTO Product(name,detail,floorprice,stepprice,[status],sellerid,PlusMinute) VALUES(@name,@detail,@floorprice,@stepprice,@status,@sellerid,0)";
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("name", productTO.Name);
            param[1] = new SqlParameter("detail", productTO.Detail);
            param[2] = new SqlParameter("floorprice", productTO.FloorPrice);
            param[3] = new SqlParameter("stepprice", productTO.StepPrice);
            param[4] = new SqlParameter("status", productTO.Status);
            param[5] = new SqlParameter("sellerid", productTO.SellerID);
            return this.ExecuteNonQuery(query, param);
        }

        public Boolean InsertProductImage(ProductImageTO productTO)
        {
            String query = "Insert INTO ProductImage(ProductID,Url) VALUES(@productId,@url)";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("productId", productTO.ProductID);
            param[1] = new SqlParameter("url", productTO.URL);
            return this.ExecuteNonQuery(query, param);
        }

        public DataTable GetLastestProduct()
        {
            String query = "SELECT TOP 1 ID FROM PRODUCT ORDER BY ID DESC";
            DataTable table = new DataTable();
            this.Fill(query, null, table);
            return table;
        }
    }
}